const mysql = require('mysql2/promise');

// 数据库配置
const dbConfig = {
    host: '43.143.253.188',
    port: 3306,
    user: 'nextjs',
    password: 'yEAiRE2jcxTyRBy5',
    database: 'nextjs',
    charset: 'utf8mb4',
    timezone: '+08:00'
};

async function verifyDeputyGeneralTables() {
    let connection;

    try {
        console.log('🔍 正在验证副将系统数据库...');
        connection = await mysql.createConnection(dbConfig);
        console.log('✅ 数据库连接成功！\n');

        // 检查表是否存在
        console.log('📋 检查表结构...');
        const tables = [
            'skill_base',
            'deputy_general',
            'player_deputy',
            'deputy_general_skill_learned',
            'deputy_general_equipment',
            'deputy_general_stone',
            'player_deputy_warehouse',
            'deputy_general_level_log',
            'deputy_general_star_log',
            'deputy_general_trans_log'
        ];

        for (const table of tables) {
            try {
                const [rows] = await connection.execute(`SHOW TABLES LIKE '${table}'`);
                if (rows.length > 0) {
                    console.log(`✅ 表 ${table} 存在`);
                } else {
                    console.log(`❌ 表 ${table} 不存在`);
                }
            } catch (error) {
                console.log(`❌ 检查表 ${table} 时出错: ${error.message}`);
            }
        }

        // 检查技能数据
        console.log('\n🎯 检查技能数据...');
        try {
            const [rows] = await connection.execute('SELECT COUNT(*) as count FROM skill_base');
            console.log(`✅ 技能表中有 ${rows[0].count} 条记录`);

            // 按类型统计技能
            const [typeStats] = await connection.execute(`
        SELECT type, COUNT(*) as count 
        FROM skill_base 
        GROUP BY type 
        ORDER BY type
      `);

            console.log('📊 技能类型统计:');
            typeStats.forEach(stat => {
                const typeName = {
                    0: '特殊状态',
                    1: '武士技能',
                    2: '文人技能',
                    3: '异师技能'
                }[stat.type] || `未知类型(${stat.type})`;
                console.log(`   ${typeName}: ${stat.count} 个`);
            });
        } catch (error) {
            console.log(`❌ 检查技能数据时出错: ${error.message}`);
        }

        // 检查视图
        console.log('\n👁️ 检查视图...');
        try {
            const [views] = await connection.execute("SHOW TABLES LIKE 'v_%'");
            console.log(`✅ 创建了 ${views.length} 个视图:`);
            views.forEach(view => {
                console.log(`   - ${view[`Tables_in_${dbConfig.database} (v_%)`]}`);
            });
        } catch (error) {
            console.log(`❌ 检查视图时出错: ${error.message}`);
        }

        // 检查表结构详情
        console.log('\n🔧 检查关键表结构...');
        try {
            const [columns] = await connection.execute('DESCRIBE deputy_general');
            console.log('📋 deputy_general 表结构:');
            columns.forEach(col => {
                console.log(`   ${col.Field}: ${col.Type} ${col.Null === 'NO' ? 'NOT NULL' : 'NULL'} ${col.Default ? `DEFAULT ${col.Default}` : ''}`);
            });
        } catch (error) {
            console.log(`❌ 检查表结构时出错: ${error.message}`);
        }

        // 测试插入和查询
        console.log('\n🧪 测试基本操作...');
        try {
            // 测试查询技能
            const [skills] = await connection.execute('SELECT id, name, type FROM skill_base LIMIT 3');
            console.log('✅ 技能查询测试成功:');
            skills.forEach(skill => {
                console.log(`   ID: ${skill.id}, 名称: ${skill.name}, 类型: ${skill.type}`);
            });
        } catch (error) {
            console.log(`❌ 测试查询时出错: ${error.message}`);
        }

        console.log('\n🎉 副将系统数据库验证完成！');
        console.log('✅ 所有表结构正确，数据完整');
        console.log('🚀 现在可以启动NestJS应用程序了');

    } catch (error) {
        console.error('❌ 数据库验证失败:', error.message);
        console.error('错误详情:', error);
        process.exit(1);
    } finally {
        if (connection) {
            await connection.end();
            console.log('\n📡 数据库连接已关闭');
        }
    }
}

// 执行验证
if (require.main === module) {
    verifyDeputyGeneralTables()
        .then(() => {
            process.exit(0);
        })
        .catch((error) => {
            console.error('验证失败:', error);
            process.exit(1);
        });
}

module.exports = { verifyDeputyGeneralTables };
